import pandas as pd
from openpyxl import load_workbook

# 定义地区列映射关系
region_columns = {
    "襄阳": "G", "安陆": "H", "云梦": "I", "孝感": "J",
    "孝昌": "K", "应城": "L", "新洲": "M", "广水": "N",
    "京山": "O", "大悟": "P", "红安": "Q", "宜城": "R",
    "枣阳": "S", "随州": "T", "荆门": "U", "襄州": "V"
}

def generate_orders(preprocessed_file='Preprocessed_Data.xlsx', template_path='gystempRestockOrder.xlsx'):
    # 读取预处理数据
    processed_df = pd.read_excel(preprocessed_file)
    
    # 按供应商分组输出
    for supplier, group in processed_df.groupby('供应商'):
        # 加载模板
        wb = load_workbook(template_path)
        ws = wb.active
        
        # 填写供应商和总件数
        ws['C2'] = supplier
        ws['C3'] = group['件数'].sum()
        
        # 从第7行开始填充数据
        row_index = 7
        for _, row in group.iterrows():
            # 填充数据
            ws[f'D{row_index}'] = row['货号']
            ws[f'E{row_index}'] = row['颜色']
            ws[f'F{row_index}'] = row['码号']
            
            # 填充地区件数
            region = row['地区']
            if region in region_columns:
                col_letter = region_columns[region]
                ws[f'{col_letter}{row_index}'] = row['件数']
            else:
                print(f"⚠️ 警告：地区 '{region}' 未映射，行{row_index}件数未填充")
            
            row_index += 1
        
        # 保存文件
        output_file = f'{supplier}_RestockOrder.xlsx'
        wb.save(output_file)
        print(f'✅ 已生成: {output_file}')
    
    print('🎉 所有供应商文件处理完成！')

# 执行模板填充
if __name__ == "__main__":
    generate_orders()